<!DOCTYPE html>
<html>

<head>
	<meta charset="utf-8">
	<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
	<meta name="theme-color" content="#33474d">
	<title>失落的乐章</title>
	<link rel="stylesheet" href="/css/style.css" />
	
      <link rel="alternate" href="/atom.xml" title="失落的乐章" type="application/atom+xml">
    
</head>

<body>

	<header class="header">
		<nav class="header__nav">
			
				<a href="/archives" class="header__link">Archive</a>
			
				<a href="/tags" class="header__link">Tags</a>
			
				<a href="/atom.xml" class="header__link">RSS</a>
			
		</nav>
		<h1 class="header__title"><a href="/">失落的乐章</a></h1>
		<h2 class="header__subtitle">技术面前，永远都是学生。</h2>
	</header>

	<main>
		
	<span class="different-posts different-posts_earlier">📖 <a href="/page/27">earlier posts</a> 📖</span>




	<article>
	
		<h1><a href="/2017/10/12/MySQL/34. 查看mysql主从复制延迟和数据中断/">查看mysql主从复制延迟和数据中断</a></h1>
	
	<div class="article__infos">
		<span class="article__date">2017-10-12</span><br />
		
		
			<span class="article__tags">
			  	<a class="article__tag-link" href="/tags/MySQL/">MySQL</a>
			</span>
		
	</div>

	

	
		<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;查看mySQL延迟的方法，查看了多个案例，大家众说纷纭，意见差不多一致。如下也是我参考别人经验做的一些测试，希望能检测到mysql复制延迟、数据中断。</p>
<h2 id="方法一：查看Seconds-Behind-Master"><a href="#方法一：查看Seconds-Behind-Master" class="headerlink" title="方法一：查看Seconds_Behind_Master"></a>方法一：查看Seconds_Behind_Master</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;该参数有如下值：</p>
<ul>
<li>NULL  表示io_thread或sql_thread有一个发生故障，就是说该线程的Running状态时No，而非Yes</li>
<li>0     表示主从复制良好，没有lag存在</li>
<li>正值  表示主从已出现延时，数字越大表示从库落后主库越多</li>
<li>负值  很罕见，是一个BUG，按理说不应该出现</li>
</ul>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;该方法是使用命令show slave status,通过比较SQL THREAD接受events时间的时间戳与IO THREAD执行事件events时间戳的差值–秒数，来确定slave落后于master多少，如主从时间不同，改时间的计算不受影响。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;众所周知备库relay-log和主库的bin-log里的内容一样，真正和主库有关两的是io_thread，当主库I/O负载很大或网络阻塞时，io_thread不能及时复制binlog，而sql_thread一直能跟上io_thread的脚步，这时seconds_behind_master的值是0，实际上却不是，这时用该值作为延迟参考则不准。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">change master to master_host=<span class="string">'192.168.2.7'</span>,master_user=<span class="string">'tongbu'</span>,master_password=<span class="string">'123456'</span>,master_log_file=<span class="string">'mysql-bin.000008'</span>,master_log_pos=291263843;</div></pre></td></tr></table></figure>
<h2 id="方法二：使用pt-heartbeat工具"><a href="#方法二：使用pt-heartbeat工具" class="headerlink" title="方法二：使用pt-heartbeat工具"></a>方法二：使用pt-heartbeat工具</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;该工具可以计算出MySQL复制或者是PostgreSQL,它可以更新master或者监控复制。它还可以从f 读取配置。它借助timestmp的比较实现的，首先需要保证主从服务器时间必须要保持一致，通过与相同的一个NTP server同步时钟。它需要在主库上创建一个heartbeat的表，里面的时间戳ts就是当前的时间戳 now()，该结构也会被复制到从库上。表建好以后，会在主库上以后台进程的模式去执行一行更新操作的命令，定期去向表中的插入数据，这 个周期默认为1 秒，同时从库也会在后台执行一个监控命令，与主库保持一致的周期+0.5S（默认0.5S延迟检查）去比较，复制过来记录的ts值与主库上的同一条ts值，差值为0表示无延时，差值越大表示 延时的秒数越多。</p>
<h2 id="使用工具前提："><a href="#使用工具前提：" class="headerlink" title="使用工具前提："></a>使用工具前提：</h2><ol>
<li>在主库上建立heartbeat表</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">pt-heartbeat -h localhost -D <span class="built_in">test</span>  --create-table --update</div></pre></td></tr></table></figure>
<ol>
<li>更新主库上的heartbeat</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">pt-heartbeat -D <span class="built_in">test</span> --master-server-id=1 --update</div></pre></td></tr></table></figure>
<ol>
<li>在从库上监控复制延迟</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div></pre></td><td class="code"><pre><div class="line">pt-heartbeat --user=tongbu --password=<span class="string">'123456'</span> -D <span class="built_in">test</span> --monitor -h 192.168.2.9 --<span class="built_in">print</span>-master-server-id</div><div class="line">0.00s [  0.00s,  0.00s,  0.00s ] 1</div><div class="line">0.00s [  0.00s,  0.00s,  0.00s ] 1</div><div class="line">0.00s [  0.00s,  0.00s,  0.00s ] 1</div><div class="line">0.00s [  0.00s,  0.00s,  0.00s ] 1</div><div class="line">0.00s [  0.00s,  0.00s,  0.00s ] 1</div><div class="line">0.00s [  0.00s,  0.00s,  0.00s ] 1</div><div class="line">0.00s [  0.00s,  0.00s,  0.00s ] 1</div><div class="line">0.00s [  0.00s,  0.00s,  0.00s ] 1</div><div class="line">0.00s [  0.00s,  0.00s,  0.00s ] 1</div><div class="line">0.01s [  0.00s,  0.00s,  0.00s ] 1</div><div class="line">0.00s [  0.00s,  0.00s,  0.00s ] 1</div><div class="line">0.00s [  0.00s,  0.00s,  0.00s ] 1</div><div class="line">0.00s [  0.00s,  0.00s,  0.00s ] 1</div><div class="line">0.00s [  0.00s,  0.00s,  0.00s ] 1</div><div class="line">0.00s [  0.00s,  0.00s,  0.00s ] 1</div><div class="line">0.00s [  0.00s,  0.00s,  0.00s ] 1</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;当然还有其他一些操作命令：</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;将主库上的update使用守护进程方式调度</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">pt-heartbeat -D <span class="built_in">test</span> --master-server-id=1 --update --daemonize</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;修改主库上的更新时间间隔为2s</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">pt-heartbeat -D <span class="built_in">test</span> --update --daemonize --interval=2</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;修改主库上的pt-heartbeat守护进程</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div></pre></td><td class="code"><pre><div class="line">pt-heartbeat --stop</div><div class="line">Successfully created file /tmp/pt-heartbeat-sentinel</div><div class="line"></div><div class="line">rm -rf /tmp/pt-heartbeat-sentinel</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;单词查看从库上的延迟情况</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">pt-heartbeat --user=tongbu --password=<span class="string">'123456'</span> -D <span class="built_in">test</span>  -h 192.168.2.9 --check</div><div class="line">0.00</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;使用守护进程监控从库并输出日志</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">pt-heartbeat --user=tongbu --password=<span class="string">'123456'</span> -h 192.168.2.9 -D <span class="built_in">test</span> --master-server-id=1 --monitor --<span class="built_in">print</span>-master-server-id --daemonize --<span class="built_in">log</span>=/var/<span class="built_in">log</span>/pt_slave_lag.log</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;如下是脚本的方式，只不过使用脚本的方式实现的。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div><div class="line">24</div><div class="line">25</div><div class="line">26</div><div class="line">27</div><div class="line">28</div><div class="line">29</div><div class="line">30</div><div class="line">31</div><div class="line">32</div><div class="line">33</div><div class="line">34</div><div class="line">35</div><div class="line">36</div><div class="line">37</div><div class="line">38</div><div class="line">39</div><div class="line">40</div><div class="line">41</div><div class="line">42</div><div class="line">43</div><div class="line">44</div><div class="line">45</div><div class="line">46</div><div class="line">47</div><div class="line">48</div><div class="line">49</div><div class="line">50</div><div class="line">51</div><div class="line">52</div><div class="line">53</div><div class="line">54</div><div class="line">55</div><div class="line">56</div><div class="line">57</div><div class="line">58</div><div class="line">59</div><div class="line">60</div><div class="line">61</div><div class="line">62</div><div class="line">63</div><div class="line">64</div><div class="line">65</div><div class="line">66</div><div class="line">67</div><div class="line">68</div><div class="line">69</div><div class="line">70</div><div class="line">71</div><div class="line">72</div><div class="line">73</div><div class="line">74</div><div class="line">75</div><div class="line">76</div><div class="line">77</div><div class="line">78</div><div class="line">79</div><div class="line">80</div><div class="line">81</div><div class="line">82</div><div class="line">83</div><div class="line">84</div><div class="line">85</div><div class="line">86</div><div class="line">87</div><div class="line">88</div><div class="line">89</div><div class="line">90</div><div class="line">91</div><div class="line">92</div><div class="line">93</div><div class="line">94</div><div class="line">95</div><div class="line">96</div><div class="line">97</div><div class="line">98</div><div class="line">99</div><div class="line">100</div><div class="line">101</div><div class="line">102</div><div class="line">103</div><div class="line">104</div><div class="line">105</div><div class="line">106</div><div class="line">107</div><div class="line">108</div><div class="line">109</div><div class="line">110</div><div class="line">111</div></pre></td><td class="code"><pre><div class="line"><span class="meta">#!/bin/sh</span></div><div class="line"><span class="comment">#description:check slave replication delay</span></div><div class="line">PT=`<span class="built_in">which</span> pt-heartbeat`</div><div class="line">ADMIN=`<span class="built_in">which</span> mysqladmin`</div><div class="line">MYSQL=`<span class="built_in">which</span> mysql`</div><div class="line">WARN=10</div><div class="line">CRITIC=20</div><div class="line">COMMON=3</div><div class="line">MASTERID=1</div><div class="line"><span class="comment">###############</span></div><div class="line"><span class="function"><span class="title">watch_update</span></span>()&#123;</div><div class="line">RE=`ps -ef|grep <span class="variable">$PT</span>|grep -v grep|grep <span class="string">"\--update"</span>`</div><div class="line"><span class="keyword">if</span> [ ! -n <span class="string">"<span class="variable">$RE</span>"</span> ];<span class="keyword">then</span></div><div class="line">   <span class="variable">$PT</span> -D <span class="built_in">test</span> --master-server-id=<span class="variable">$MASTERID</span> --update --daemonize</div><div class="line"><span class="keyword">fi</span></div><div class="line">&#125;</div><div class="line"><span class="comment">################</span></div><div class="line"><span class="function"><span class="title">watch_mysql</span></span>()&#123;</div><div class="line">SAFE_STATUS=`ps -ef|grep -w mysqld_safe|grep -v grep`</div><div class="line">MYSQLD_STATUS=`ps -ef|grep -w mysqld|grep -v grep`</div><div class="line"><span class="keyword">if</span> [ ! -n <span class="string">"<span class="variable">$SAFE_STATUS</span>"</span> ];<span class="keyword">then</span></div><div class="line">   <span class="built_in">echo</span> <span class="string">"Mysqld_safe doesn't running,Please check your mysqld_safe status"</span></div><div class="line">   <span class="built_in">exit</span> 1</div><div class="line"><span class="keyword">fi</span></div><div class="line"><span class="keyword">if</span> [ ! -n <span class="string">"<span class="variable">$MYSQLD_STATUS</span>"</span> ];<span class="keyword">then</span></div><div class="line">   <span class="built_in">echo</span> <span class="string">"Mysqld program status --stop,Please check your mysqld status "</span></div><div class="line">   <span class="built_in">exit</span> 1</div><div class="line"><span class="keyword">fi</span></div><div class="line">&#125;</div><div class="line"><span class="comment">####################</span></div><div class="line"><span class="function"><span class="title">watch_mysql_slave</span></span>()&#123;</div><div class="line">REP_STATUS=`<span class="variable">$ADMIN</span> processlist|grep <span class="string">"Binlog Dump"</span>`</div><div class="line"><span class="keyword">if</span> [ ! -n <span class="string">"<span class="variable">$REP_STATUS</span>"</span> ];<span class="keyword">then</span></div><div class="line">    <span class="built_in">echo</span> <span class="string">"slave process doesn't running,Please check your replication"</span></div><div class="line">    <span class="built_in">exit</span> 1</div><div class="line"><span class="keyword">fi</span></div><div class="line">&#125;</div><div class="line"><span class="comment">#################</span></div><div class="line"><span class="function"><span class="title">enter_slave_info</span></span>()&#123;</div><div class="line">  <span class="built_in">echo</span> <span class="string">"please enter your slave username:"</span></div><div class="line">  <span class="built_in">read</span> NAME</div><div class="line">  <span class="keyword">if</span> [  -n <span class="string">"<span class="variable">$NAME</span>"</span> ];<span class="keyword">then</span></div><div class="line">      <span class="built_in">echo</span> <span class="string">" you enter username is: <span class="variable">$NAME</span>"</span></div><div class="line">  <span class="keyword">fi</span></div><div class="line">  </div><div class="line">  <span class="built_in">echo</span> <span class="string">"please enter your slave user password:"</span></div><div class="line">  <span class="built_in">read</span> PASS</div><div class="line">  <span class="keyword">if</span> [ -n <span class="string">"<span class="variable">$PASS</span>"</span> ];<span class="keyword">then</span></div><div class="line">      <span class="built_in">echo</span> <span class="string">"you enter user password is: <span class="variable">$PASS</span>"</span></div><div class="line">  <span class="keyword">fi</span>  </div><div class="line">  </div><div class="line">  <span class="built_in">echo</span> <span class="string">"please enter your slave hostname or address:"</span></div><div class="line">  <span class="built_in">read</span> ADDR</div><div class="line">  <span class="keyword">if</span> [ -n <span class="string">"<span class="variable">$ADDR</span>"</span> ];<span class="keyword">then</span></div><div class="line">      <span class="built_in">echo</span> <span class="string">"you enter slave hostname or address is: <span class="variable">$ADDR</span>"</span></div><div class="line">  <span class="keyword">fi</span></div><div class="line">&#125;</div><div class="line"><span class="comment">##################</span></div><div class="line"><span class="function"><span class="title">watch_slave_delay</span></span>()&#123;</div><div class="line"><span class="comment">#  echo "please enter your watch options(1.check 2.monitor)"</span></div><div class="line"><span class="comment">#  read OPTION</span></div><div class="line"><span class="comment">#  if  [ $OPTION -eq 1 ];then</span></div><div class="line">      SLAVE_DELAY=`<span class="variable">$PT</span> --user=<span class="variable">$NAME</span> --password=<span class="string">"<span class="variable">$PASS</span>"</span> -h <span class="variable">$ADDR</span> -D <span class="built_in">test</span> --master-server-id=<span class="variable">$MASTERID</span> --check --<span class="built_in">print</span>-master-server-id`</div><div class="line"><span class="comment">#  elif [ $OPTION -eq 2 ];then</span></div><div class="line"><span class="comment">#      `$PT --user=$NAME --password="PASS" -h $ADDR -D test --master-server-id=$MASTERID --monitor --print-master-server-id`</span></div><div class="line"><span class="comment">#  else</span></div><div class="line"><span class="comment">#       echo "your enter are error,now EXIT"</span></div><div class="line"><span class="comment">#       exit 1</span></div><div class="line"><span class="comment">#  fi</span></div><div class="line"><span class="keyword">if</span> [ ! -n <span class="string">"<span class="variable">$SLAVE_DELAY</span>"</span> ];<span class="keyword">then</span></div><div class="line">   <span class="built_in">echo</span> <span class="string">"Your pt-heartbeat tool must haven't install or you username,password,slave hostname ERROR"</span></div><div class="line">   <span class="built_in">exit</span> 1</div><div class="line"><span class="keyword">else</span></div><div class="line">   <span class="built_in">echo</span> <span class="string">"DELAY TIME:  <span class="variable">$SLAVE_DELAY</span>"</span></div><div class="line"><span class="keyword">fi</span>  </div><div class="line">&#125;</div><div class="line"></div><div class="line"><span class="comment">##################################</span></div><div class="line"><span class="function"><span class="title">watch_slave_interrupt</span></span>()&#123;</div><div class="line">  INT_RE=`<span class="variable">$MYSQL</span> -s -u <span class="variable">$NAME</span> -p<span class="string">"<span class="variable">$PASS</span>"</span> -h <span class="variable">$ADDR</span> -e <span class="string">'show slave status\G'</span>|grep <span class="string">"Last_Error:"</span>`</div><div class="line">  <span class="keyword">if</span> [ -n <span class="string">"<span class="variable">$INT_RE</span>"</span> ];<span class="keyword">then</span></div><div class="line">    <span class="built_in">echo</span> <span class="string">"<span class="variable">$INT_RE</span>"</span></div><div class="line">  <span class="keyword">else</span></div><div class="line">    <span class="built_in">echo</span>  <span class="string">"INTERUUPT Info: "</span></div><div class="line">    <span class="built_in">echo</span>  <span class="string">"<span class="variable">$INT_RE</span>"</span></div><div class="line">  <span class="keyword">fi</span></div><div class="line">&#125;</div><div class="line"><span class="comment">########################</span></div><div class="line"><span class="function"><span class="title">WATCH</span></span>()&#123;</div><div class="line">watch_slave_delay</div><div class="line">watch_slave_interrupt</div><div class="line">&#125;</div><div class="line"></div><div class="line"><span class="comment">#########################</span></div><div class="line"><span class="function"><span class="title">START_WATCH</span></span>()&#123;</div><div class="line">  watch_mysql</div><div class="line">  watch_mysql_slave</div><div class="line">  watch_update</div><div class="line">  enter_slave_info </div><div class="line">&#125;</div><div class="line"><span class="comment">##########################</span></div><div class="line"><span class="function"><span class="title">LOOP_WATCH</span></span>()&#123;</div><div class="line">  START_WATCH</div><div class="line"><span class="built_in">echo</span> <span class="string">"+++++++++++++ DELAY INFO  ++++++++++++++++"</span></div><div class="line">  <span class="keyword">while</span> <span class="literal">true</span>;<span class="keyword">do</span></div><div class="line">    WATCH</div><div class="line">    sleep 30</div><div class="line">  <span class="keyword">done</span></div><div class="line">&#125;</div><div class="line"><span class="comment">##########################</span></div><div class="line">LOOP_WATCH</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;脚本运行结果如下：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div></pre></td><td class="code"><pre><div class="line">[root@mjx mjx]<span class="comment"># ./delay.sh </span></div><div class="line">please enter your slave username:</div><div class="line">tongbu</div><div class="line">you enter username is: tongbu</div><div class="line">please enter your slave user password:</div><div class="line">123456</div><div class="line">you enter user password is: 123456</div><div class="line">please enter your slave hostname or address:</div><div class="line">192.168.2.9</div><div class="line">you enter slave hostname or address is: 192.168.2.9</div><div class="line">+++++++++++++ DELAY INFO  ++++++++++++++++</div><div class="line">DELAY TIME:  0.36 1</div><div class="line">Warning: Using a password on the <span class="built_in">command</span> line interface can be insecure.</div><div class="line">                   Last_Error: </div><div class="line">DELAY TIME:  0.96 1</div><div class="line">Warning: Using a password on the <span class="built_in">command</span> line interface can be insecure.</div><div class="line">                   Last_Error: </div><div class="line">DELAY TIME:  0.12 1</div><div class="line">Warning: Using a password on the <span class="built_in">command</span> line interface can be insecure.</div><div class="line">                   Last_Error: </div><div class="line">DELAY TIME:  1.91 1</div><div class="line">Warning: Using a password on the <span class="built_in">command</span> line interface can be insecure.</div><div class="line">                   Last_Error:</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;但是输出结果没法把报错屏蔽掉。</p>

	

	

</article>




	<article>
	
		<h1><a href="/2017/10/12/MySQL/33. mysql主从数据库不同步的2种解决方法/">mysql主从数据库不同步的2种解决方法</a></h1>
	
	<div class="article__infos">
		<span class="article__date">2017-10-12</span><br />
		
		
			<span class="article__tags">
			  	<a class="article__tag-link" href="/tags/MySQL/">MySQL</a>
			</span>
		
	</div>

	

	
		<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;发现Mysql的主从数据库没有同步 </p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;先上Master库： </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt;show processlist;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;查看下进程是否Sleep太多。发现很正常。 </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">show master status;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;也正常。 </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show master status; </div><div class="line">+-------------------+----------+--------------+-------------------------------+ </div><div class="line">| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | </div><div class="line">+-------------------+----------+--------------+-------------------------------+ </div><div class="line">| mysqld-bin.000001 | 3260 | | mysql,<span class="built_in">test</span>,information_schema | </div><div class="line">+-------------------+----------+--------------+-------------------------------+ </div><div class="line">1 row <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;再到Slave上查看 </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show slave status\G;</div><div class="line">Slave_IO_Running: Yes </div><div class="line">Slave_SQL_Running: No</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;可见是Slave不同步 </p>
<h2 id="下面介绍两种解决方法："><a href="#下面介绍两种解决方法：" class="headerlink" title="下面介绍两种解决方法："></a>下面介绍两种解决方法：</h2><h3 id="方法一：忽略错误后，继续同步"><a href="#方法一：忽略错误后，继续同步" class="headerlink" title="方法一：忽略错误后，继续同步"></a>方法一：忽略错误后，继续同步</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;该方法适用于主从库数据相差不大，或者要求数据可以不完全统一的情况，数据要求不严格的情况 </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div></pre></td><td class="code"><pre><div class="line">stop slave; </div><div class="line"><span class="comment">#表示跳过一步错误，后面的数字可变 </span></div><div class="line"><span class="built_in">set</span> global sql_slave_skip_counter =1; </div><div class="line">start slave;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;之后再用</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show slave status\G;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;查看： </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">Slave_IO_Running: Yes </div><div class="line">Slave_SQL_Running: Yes</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;ok，现在主从同步状态正常了。。。 </p>
<h3 id="方式二：重新做主从，完全同步"><a href="#方式二：重新做主从，完全同步" class="headerlink" title="方式二：重新做主从，完全同步"></a>方式二：重新做主从，完全同步</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;该方法适用于主从库数据相差较大，或者要求数据完全统一的情况 </p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;解决步骤如下： </p>
<ol>
<li>先进入主库，进行锁表，防止数据写入 </li>
</ol>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;使用命令： </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; flush tables with <span class="built_in">read</span> lock;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;注意：该处是锁定为只读状态，语句不区分大小写 </p>
<ol>
<li>进行数据备份 </li>
</ol>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;把数据备份到mysql.bak.sql文件 </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@server01 mysql]<span class="comment">#mysqldump -uroot -p -hlocalhost &gt; mysql.bak.sql</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;这里注意一点：数据库备份一定要定期进行，可以用shell脚本或者python脚本，都比较方便，确保数据万无一失 </p>
<ol>
<li>查看master 状态 </li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show master status; </div><div class="line">+-------------------+----------+--------------+-------------------------------+ </div><div class="line">| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | </div><div class="line">+-------------------+----------+--------------+-------------------------------+ </div><div class="line">| mysqld-bin.000001 | 3260 | | mysql,<span class="built_in">test</span>,information_schema | </div><div class="line">+-------------------+----------+--------------+-------------------------------+ </div><div class="line">1 row <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</div></pre></td></tr></table></figure>
<ol>
<li>把mysql备份文件传到从库机器，进行数据恢复 </li>
</ol>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;使用scp命令 </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@server01 mysql]<span class="comment"># scp mysql.bak.sql root@192.168.128.101:/tmp/</span></div></pre></td></tr></table></figure>
<ol>
<li>停止从库的状态 </li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; stop slave;</div></pre></td></tr></table></figure>
<ol>
<li>然后到从库执行mysql命令，导入数据备份 </li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; <span class="built_in">source</span> /tmp/mysql.bak.sql</div></pre></td></tr></table></figure>
<ol>
<li>设置从库同步，注意该处的同步点，就是主库show master status信息里的| File| Position两项 </li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">change master to master_host = <span class="string">'192.168.128.100'</span>, master_user = <span class="string">'rsync'</span>, master_port=3306, master_password=<span class="string">''</span>, master_log_file = <span class="string">'mysqld-bin.000001'</span>, master_log_pos=3260;</div></pre></td></tr></table></figure>
<ol>
<li>重新开启从同步 </li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; stop slave;</div></pre></td></tr></table></figure>
<ol>
<li>查看同步状态 </li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show slave status\G; </div><div class="line">Slave_IO_Running: Yes </div><div class="line">Slave_SQL_Running: Yes</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;好了，同步完成啦。</p>

	

	

</article>




	<article>
	
		<h1><a href="/2017/10/12/MySQL/32. mysql一主多从同步配置/">mysql一主多从同步配置</a></h1>
	
	<div class="article__infos">
		<span class="article__date">2017-10-12</span><br />
		
		
			<span class="article__tags">
			  	<a class="article__tag-link" href="/tags/MySQL/">MySQL</a>
			</span>
		
	</div>

	

	
		<h2 id="一、环境"><a href="#一、环境" class="headerlink" title="一、环境"></a>一、环境</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;master：192.168.101<br>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;MYSQL版本：5.1.48-community-log</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;slave1:192.168.2.182<br>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;MYSQL版本：5.1.48-community-log</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;slave2:192.168.2.111<br>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;MYSQL版本：5.1.48-community-log</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;so…1 vs 2。</p>
<h2 id="二、master和-slave上的相关配置"><a href="#二、master和-slave上的相关配置" class="headerlink" title="二、master和 slave上的相关配置"></a>二、master和 slave上的相关配置</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;3台上都一样：</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;<strong>在/etc目录下可能无my.cnf文件，从/user/share/mysql目录中拷贝my-medium.cnf 到/etc并修改成my.cnf</strong></p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div></pre></td><td class="code"><pre><div class="line">[root@localhost etc]<span class="comment"># cp /usr/share/mysql/my-medium.cnf my.cnf</span></div><div class="line">[root@localhost etc]<span class="comment"># ll |grep my</span></div><div class="line">-rwxr-xr-x 1 root root 5204 Feb 13 22:52 my_bak</div><div class="line">-rwxr-xr-x 1 root root 4765 Jul 10 23:07 my.cnf</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;master 上;</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@mysql101 ~]<span class="comment"># vi /etc/my.cnf</span></div></pre></td></tr></table></figure>
<h3 id="1-修改master上的配置文件my-cnf。"><a href="#1-修改master上的配置文件my-cnf。" class="headerlink" title="1.修改master上的配置文件my.cnf。"></a>1.修改master上的配置文件my.cnf。</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在[mysqld]下添加如下字段：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div></pre></td><td class="code"><pre><div class="line">server-id = 1</div><div class="line"><span class="built_in">log</span>-bin=mysql-bin</div><div class="line">binlog-do-db=YYY //需要同步的数据库</div><div class="line">binlog-ignore-db=mysql //被忽略的数据库</div><div class="line">binlog-ignore-db=information-schema //被忽略的数据库</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在master上为slave添加一个同步账号</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div></pre></td><td class="code"><pre><div class="line">mysql&gt; grant replication slave on *.* to <span class="string">'affairlog'</span>@<span class="string">'192.168.2.182'</span> identified by <span class="string">'pwd123'</span>;</div><div class="line">//在slave1上登陆成功</div><div class="line">mysql&gt; grant replication slave on *.* to <span class="string">'affairlog'</span>@<span class="string">'192.168.2.111'</span> identified by <span class="string">'pwd123'</span>;</div><div class="line">//在slave2上登陆成功</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;保存后，重启master的mysql服务：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">service mysql restart;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;用show master status命令查看日志情况</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show master status\G;</div><div class="line">*************************** 1. row ***************************</div><div class="line">File: mysql-bin.000087</div><div class="line">Position: 106</div><div class="line">Binlog_Do_DB: YYY</div><div class="line">Binlog_Ignore_DB: mysql,information-schema</div><div class="line">1 row <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</div></pre></td></tr></table></figure>
<h3 id="2-修改slave1上的配置文件my-cnf。"><a href="#2-修改slave1上的配置文件my-cnf。" class="headerlink" title="2.修改slave1上的配置文件my.cnf。"></a>2.修改slave1上的配置文件my.cnf。</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在[mysqld]下添加如下字段</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div></pre></td><td class="code"><pre><div class="line">[root@mysql182 ~]<span class="comment"># vi /etc/my.cnf</span></div><div class="line">server-id=182</div><div class="line">master-host=192.168.3.101</div><div class="line">master-user= affairlog</div><div class="line">master-password=pwd123</div><div class="line">master-port=3306</div><div class="line">master-connect-retry=60</div><div class="line">replicate-do-db=YYY //同步的数据库</div><div class="line">replicate-ignore-db=mysql //被忽略的数据库</div><div class="line">replicate-ignore-db=information-schema //被忽略的数据库</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;保存后，重启slave的mysql服务：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">service mysql restart;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;修改slave2上的配置文件my.cnf，和上面类似，只是把server-id改下，为了方便，我都用了相应的ip某位，</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;所以，slave2上我设置的server-id是111。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在进入两个slave机中的mysql。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div><div class="line">24</div><div class="line">25</div><div class="line">26</div><div class="line">27</div><div class="line">28</div><div class="line">29</div><div class="line">30</div><div class="line">31</div><div class="line">32</div><div class="line">33</div><div class="line">34</div><div class="line">35</div><div class="line">36</div><div class="line">37</div><div class="line">38</div><div class="line">39</div><div class="line">40</div><div class="line">41</div><div class="line">42</div></pre></td><td class="code"><pre><div class="line">mysql&gt;start slave;</div><div class="line">mysql&gt;show slave status\G;</div><div class="line">*************************** 1. row ***************************</div><div class="line">Slave_IO_State: Waiting <span class="keyword">for</span> master to send event</div><div class="line">Master_Host: 192.168.3.101</div><div class="line">Master_User: affairlog</div><div class="line">Master_Port: 3306</div><div class="line">Connect_Retry: 60</div><div class="line">Master_Log_File: mysql-bin.000087</div><div class="line">Read_Master_Log_Pos: 106</div><div class="line">Relay_Log_File: vm111-relay-bin.000002</div><div class="line">Relay_Log_Pos: 251</div><div class="line">Relay_Master_Log_File: mysql-bin.000087</div><div class="line">Slave_IO_Running: Yes</div><div class="line">Slave_SQL_Running: Yes</div><div class="line">Replicate_Do_DB: YYY</div><div class="line">Replicate_Ignore_DB: mysql,information-schema</div><div class="line">Replicate_Do_Table:</div><div class="line">Replicate_Ignore_Table:</div><div class="line">Replicate_Wild_Do_Table:</div><div class="line">Replicate_Wild_Ignore_Table:</div><div class="line">Last_Errno: 0</div><div class="line">Last_Error:</div><div class="line">Skip_Counter: 0</div><div class="line">Exec_Master_Log_Pos: 106</div><div class="line">Relay_Log_Space: 406</div><div class="line">Until_Condition: None</div><div class="line">Until_Log_File:</div><div class="line">Until_Log_Pos: 0</div><div class="line">Master_SSL_Allowed: No</div><div class="line">Master_SSL_CA_File:</div><div class="line">Master_SSL_CA_Path:</div><div class="line">Master_SSL_Cert:</div><div class="line">Master_SSL_Cipher:</div><div class="line">Master_SSL_Key:</div><div class="line">Seconds_Behind_Master: 0</div><div class="line">Master_SSL_Verify_Server_Cert: No</div><div class="line">Last_IO_Errno: 0</div><div class="line">Last_IO_Error:</div><div class="line">Last_SQL_Errno: 0</div><div class="line">Last_SQL_Error:</div><div class="line">1 row <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;<strong>如果两个slave中的Slave_IO_Running、Slave_SQL_Running状态均为Yes则表明设置成功。</strong></p>
<h2 id="三、可能遇到的问题："><a href="#三、可能遇到的问题：" class="headerlink" title="三、可能遇到的问题："></a>三、可能遇到的问题：</h2><h3 id="问题1：Slave-IO-Running-No或者Slave-SQL-Running-No"><a href="#问题1：Slave-IO-Running-No或者Slave-SQL-Running-No" class="headerlink" title="问题1：Slave_IO_Running: No或者Slave_SQL_Running: No"></a>问题1：Slave_IO_Running: No或者Slave_SQL_Running: No</h3><h4 id="停掉slave服务"><a href="#停掉slave服务" class="headerlink" title="停掉slave服务"></a>停掉slave服务</h4><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">mysql&gt; slave stop;</div><div class="line">Query OK, 0 rows affected (2.01 sec)</div></pre></td></tr></table></figure>
<h4 id="解决办法"><a href="#解决办法" class="headerlink" title="解决办法"></a>解决办法</h4><ol>
<li>在master上查看。</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show master status\G;</div><div class="line">*************************** 1. row ***************************</div><div class="line">File: mysql-bin.000087</div><div class="line">Position: 1845</div><div class="line">Binlog_Do_DB: YYY</div><div class="line">Binlog_Ignore_DB: mysql,information-schema</div><div class="line">1 row <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</div></pre></td></tr></table></figure>
<ol>
<li>到slave上手动同步。</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line">mysql&gt;change master to</div><div class="line">&gt;master_host=<span class="string">'192.168.3.101'</span>,</div><div class="line">&gt;master_user=<span class="string">'affairlog'</span>,</div><div class="line">&gt;master_password=<span class="string">'pwd123'</span>,</div><div class="line">&gt;master_log_file=<span class="string">'mysql-bin.000087'</span>,</div><div class="line">&gt;master_log_pos=1845;</div><div class="line">Query OK, 0 rows affected (0.00 sec)</div></pre></td></tr></table></figure>
<h4 id="启动slave服务"><a href="#启动slave服务" class="headerlink" title="启动slave服务"></a>启动slave服务</h4><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; slave start;</div></pre></td></tr></table></figure>
<h4 id="再次查看Slave-IO-Running、Slave-SQL-Running状态，为Yes则表明设置成功。"><a href="#再次查看Slave-IO-Running、Slave-SQL-Running状态，为Yes则表明设置成功。" class="headerlink" title="再次查看Slave_IO_Running、Slave_SQL_Running状态，为Yes则表明设置成功。"></a>再次查看Slave_IO_Running、Slave_SQL_Running状态，为Yes则表明设置成功。</h4><h3 id="问题2：RROR-1198-HY000-This-operation-cannot-be-performed-with-a-running-slave-run-STOP-SLAVE-first"><a href="#问题2：RROR-1198-HY000-This-operation-cannot-be-performed-with-a-running-slave-run-STOP-SLAVE-first" class="headerlink" title="问题2：RROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first"></a>问题2：RROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;错误重现：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line">mysql&gt;change master to</div><div class="line">&gt;master_host=<span class="string">'192.168.3.101'</span>,</div><div class="line">&gt;master_user=<span class="string">'affairlog'</span>,</div><div class="line">&gt;master_password=<span class="string">'pwd123'</span>,</div><div class="line">&gt;master_log_file=<span class="string">'mysql-bin.000087'</span>,</div><div class="line">&gt;master_log_pos=1845;</div><div class="line">ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;解决方法：</p>
<ol>
<li>停掉slave服务</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">mysql&gt; slave stop;</div><div class="line">Query OK, 0 rows affected (2.01 sec)</div></pre></td></tr></table></figure>
<ol>
<li>重置slave服务</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">mysql&gt; reset stop;</div><div class="line">Query OK, 0 rows affected (2.01 sec)</div></pre></td></tr></table></figure>
<ol>
<li>再执行一次change命令</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line">mysql&gt;change master to</div><div class="line">&gt;master_host=<span class="string">'192.168.3.101'</span>,</div><div class="line">&gt;master_user=<span class="string">'affairlog'</span>,</div><div class="line">&gt;master_password=<span class="string">'pwd123'</span>,</div><div class="line">&gt;master_log_file=<span class="string">'mysql-bin.000087'</span>,</div><div class="line">&gt;master_log_pos=1845;</div><div class="line">Query OK, 0 rows affected (0.00 sec)</div></pre></td></tr></table></figure>
<ol>
<li>启动slave服务</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; slave start;</div></pre></td></tr></table></figure>
<p>5．再次查看Slave_IO_Running、Slave_SQL_Running状态，为Yes则表明设置成功。</p>
<h3 id="PS："><a href="#PS：" class="headerlink" title="PS："></a>PS：</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Slave_IO_Running：连接到主库，并读取主库的日志到本地，生成本地日志文件</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Slave_SQL_Running:读取本地日志文件，并执行日志里的SQL命令。</p>

	

	

</article>




	<article>
	
		<h1><a href="/2017/10/12/MySQL/30. mysql架构由小变大的演变过程/">mysql架构由小变大的演变过程</a></h1>
	
	<div class="article__infos">
		<span class="article__date">2017-10-12</span><br />
		
		
			<span class="article__tags">
			  	<a class="article__tag-link" href="/tags/MySQL/">MySQL</a>
			</span>
		
	</div>

	

	
		<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;假设一个网站（discuz）从最开始访问量很小做到日pv千万，我们来推测一下它的mysql服务器架构演变过程。</p>
<h2 id="第一阶段"><a href="#第一阶段" class="headerlink" title="第一阶段"></a>第一阶段</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;网站访问量日pv量级在1w以下。<strong>单台机器跑web和db</strong>，不需要做架构层调优（比如，不需要增加memcached缓存）。此时，数据往往都是每日冷备份的，但有时候如果考虑数据安全性，会搭建一个mysql主从。</p>
<h2 id="第二阶段"><a href="#第二阶段" class="headerlink" title="第二阶段"></a>第二阶段</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;网站访问量日pv达到几万。此时单台机器已经有点负载，<strong>需要我们把web和db分开</strong>，需要搭建memcached服务作为缓存。也就是说，在这个阶段，我们还可以使用单台机器跑mysql去承担整个网站的数据存储和查询。如果做mysql主从，目的也是为了数据安全性。</p>
<h2 id="第三阶段"><a href="#第三阶段" class="headerlink" title="第三阶段"></a>第三阶段</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;网站访问量日pv达到几十万。单台机器虽然也可以支撑，但是需要的机器配置要比之前的机器好很多。如果经费允许，可以购买配置很高的机器来跑mysql服务，但是并不是说，配置翻倍，性能也翻倍，到了一定阶段配置增加已经不能带来性能的增加。所以，此阶段，我们会想到做mysql服务的集群，也就是说我们可以拿多台机器跑mysql。但，mysql的集群和web集群是不一样的，我们需要考虑数据的一致性，所以不能简单套用做web集群的方式（lvs，nginx代理）。可以做的架构是，<strong>mysql主从，一主多从</strong>。为了保证架构的健壮和数据完整，主只能是一个，从可以是多个。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;还有一个问题，我们需要想到，就是在前端web层，我们的程序里面指定了mysql机器的ip，那么当mysql机器有多台时，程序里面如何去配置？discuz，其实有一个功能，支持mysql读写分离。即，我们可以拿多台机器跑mysql，其中一台写，其他多台是读，我们只需要把读和写的ip分别配置到程序中，程序自动会去区分机器。当然，如果不使用discuz自带的配置，我们还可以引用一个软件叫做 <strong>mysql-proxy</strong>, 使用他来实现读写分离。它支持一主多从的模式。</p>
<h2 id="第四阶段"><a href="#第四阶段" class="headerlink" title="第四阶段"></a>第四阶段</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;网站访问量日pv到几百万。之前的一主多从模式已经遇到瓶颈，因为当网站访问量变大，读数据库的量也会越来越大，我们需要多加一些从进来，但是从的数量增加到数十台时，由于主需要把bin-log全部分发到所有从上，那么这个过程本身就是一件很繁琐的事情，再加上频繁读取，势必会造成从上同步过来的数据有很大延迟。所以，我们可以做一个优化， <strong>把mysql原来的一主多从变为一主一从，然后从作为其他从的主，而前面的主只负责网站业务的写入，而后面的从不负责网站任何业务，只负责给其他从同步bin-log</strong>。这样还可以继续多叠加几个从库。</p>
<h2 id="第五阶段"><a href="#第五阶段" class="headerlink" title="第五阶段"></a>第五阶段</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;网站访问量日pv到1千万的时候，我们发现，网站的写入量非常大，我们之前架构中只有一个主，这里的主已经成为瓶颈了。所以，需要再近一步做出调整。比如，我们可以把业务分模块，把用户相关的单独分离出来，把权限、积分等也可以分离出来单独跑一个库，然后再做主从，也就是所谓的分库。当然也可以换一个纬度，把访问量或者写入量大的表单独分离出来，跑在一台服务器上，也可以把一个表分成多个小表。这一步操作，涉及到一些程序上的改动，所以需要事先和开发同事做好沟通和设计。总之，这一步要做的就是<strong>分库分表</strong>。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;再往后发展，继续把大表分小表即可。 而国内阿里淘宝网站的数据量是巨量的，他们的数据库全部都是mysql，他们的mysql架构就是遵循分库分表这个原则的，只不过他们划分规则会有很多纬度，比如可以根据地域划分，可以根据买家、卖家划分，可以根据时间划分等等。</p>

	

	

</article>




	<article>
	
		<h1><a href="/2017/10/12/MySQL/31. mysql5.1的RBR和SBR的优缺点/">mysql5.1的RBR和SBR的优缺点</a></h1>
	
	<div class="article__infos">
		<span class="article__date">2017-10-12</span><br />
		
		
			<span class="article__tags">
			  	<a class="article__tag-link" href="/tags/MySQL/">MySQL</a>
			</span>
		
	</div>

	

	
		<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;MySQL 5.1 中，在复制方面的改进就是引进了新的复制技术：基于行的复制。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;简言之，这种新技术就是关注表中发生变化的记录，而非以前的照抄 binlog 模式。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;从 MySQL 5.1.12 开始，可以用以下三种模式来实现：</p>
<ul>
<li>基于SQL语句的复制(statement-based replication, SBR)</li>
<li>基于行的复制(row-based replication, RBR)</li>
<li>混合模式复制(mixed-based replication, MBR)。</li>
</ul>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;相应地，binlog的格式也有三种：</p>
<ul>
<li>STATEMENT</li>
<li>ROW</li>
<li>MIXED</li>
</ul>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;MBR 模式中，SBR 模式是默认的。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在运行时可以动态的改变binlog的格式，以下几种情况不能动态修改binlog格式：</p>
<ul>
<li>存储过程或者触发器中间。 </li>
<li>使用NDB engine类型的表</li>
<li>。 当前会话试用RBR模式，并且已打开了临时表。 </li>
</ul>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;如果binlog采用了MIXED 模式，那么在以下几种情况下会自动将binlog的模式由 SBR模式改成RBR模式。</p>
<ul>
<li>当DML语句更新一个NDB engine表时。 </li>
<li>当写操作中包含UUID()函数时。 </li>
<li>2个或两个以上包含AUTO_INCREMENT字段的表被更新时。 </li>
<li>进行INSERT DELAYED语句操作数据时。 </li>
<li>使用UDF时。 </li>
<li>视图中必须要求使用RBR时，例如创建视图是使用了UUID()函数。</li>
</ul>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;设定主从复制模式的方法非常简单，只要在以前设定复制配置的基础上，再加一个参数：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">binlog_format=<span class="string">'STATEMENT'</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;或</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">binlog_format=<span class="string">'ROW'</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;或</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">binlog_format=<span class="string">'MIXED'</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在线动态修改binlog的格式。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div></pre></td><td class="code"><pre><div class="line">mysql&gt; SET SESSION binlog_format = <span class="string">'STATEMENT'</span>;</div><div class="line">mysql&gt; SET SESSION binlog_format = <span class="string">'ROW'</span>;</div><div class="line">mysql&gt; SET SESSION binlog_format = <span class="string">'MIXED'</span>;</div><div class="line">mysql&gt; SET GLOBAL binlog_format = <span class="string">'STATEMENT'</span>;</div><div class="line">mysql&gt; SET GLOBAL bin log_format = <span class="string">'ROW'</span>;</div><div class="line">mysql&gt; SET GLOBAL binlog_format = <span class="string">'MIXED'</span>;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;SBR和RBR两种模式各自的优缺点：</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;SBR 的优点：</p>
<ul>
<li>历史悠久，技术成熟。</li>
<li>binlog文件较小。</li>
<li>binlog中包含了所有数据库更改信息，可以据此来审核数据库的安全等情况。 </li>
<li>binlog可以用于实时的还原，而不仅仅用于复制。 </li>
<li>主从版本可以不一样，从服务器版本可以比主服务器版本高。</li>
</ul>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;SBR 的缺点：</p>
<ul>
<li>不是所有的UPDATE语句都能被复制，尤其是包含不确定操作的时候。 </li>
<li>调用具有不确定因素的 UDF 时复制也可能出问题 </li>
<li>使用以下函数的语句也无法被复制：<ul>
<li>LOAD_FILE()</li>
<li>UUID()</li>
<li>USER()</li>
<li>FOUND_ROWS()</li>
<li>SYSDATE() (除非启动时启用了 –sysdate-is-now 选项) </li>
</ul>
</li>
<li>INSERT … SELECT 会产生比 RBR 更多的行级锁</li>
<li>复制需要进行全表扫描(WHERE 语句中没有使用到索引)的 UPDATE 时，需要比 RBR 请求更多的行级锁 </li>
<li>对于有 AUTO_INCREMENT 字段的 InnoDB表而言，INSERT 语句会阻塞其他 INSERT 语句 </li>
<li>对于一些复杂的语句，在从服务器上的耗资源情况会更严重，而 RBR 模式下，只会对那个发生变化的记录产生影响 </li>
<li>存储函数(不是存储过程)在被调用的同时也会执行一次 NOW() 函数，这个可以说是坏事也可能是好事 </li>
<li>确定了的 UDF 也需要在从服务器上执行 </li>
<li>数据表必须几乎和主服务器保持一致才行，否则可能会导致复制出错 </li>
<li>执行复杂语句如果出错的话，会消耗更多资源</li>
</ul>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;RBR 的优点：</p>
<ul>
<li>任何情况都可以被复制，这对复制来说是最安全可靠的 </li>
<li>和其他大多数数据库系统的复制技术一样 </li>
<li>多数情况下，从服务器上的表如果有主键的话，复制就会快了很多 </li>
<li>复制以下几种语句时的行锁更少：<ul>
<li>INSERT … SELECT</li>
<li>包含 AUTO_INCREMENT 字段的 INSERT</li>
<li>没有附带条件或者并没有修改很多记录的 UPDATE 或 DELETE 语句 </li>
</ul>
</li>
<li>执行 INSERT，UPDATE，DELETE 语句时锁更少 - </li>
<li>从服务器上采用多线程来执行复制成为可能</li>
</ul>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;RBR 的缺点：</p>
<ul>
<li>binlog 大了很多 </li>
<li>复杂的回滚时 binlog 中会包含大量的数据 </li>
<li>主服务器上执行 UPDATE 语句时，所有发生变化的记录都会写到 binlog 中，而 SBR 只会写一次，这会导致频繁发生 binlog 的并发写问题 </li>
<li>UDF 产生的大 BLOB 值会导致复制变慢 </li>
<li>无法从 binlog 中看到都复制了写什么语句 </li>
<li>当在非事务表上执行一段堆积的SQL语句时，最好采用 SBR 模式，否则很容易导致主从服务器的数据不一致情况发生</li>
</ul>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;另外，针对系统库 mysql 里面的表发生变化时的处理规则如下：</p>
<ul>
<li>如果是采用 INSERT，UPDATE，DELETE 直接操作表的情况，则日志格式根据 binlog_format 的设定而记录 </li>
<li>如果是采用 GRANT，REVOKE，SET PASSWORD 等管理语句来做的话，那么无论如何都采用 SBR 模式记录 </li>
</ul>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;注：采用 RBR 模式后，能解决很多原先出现的主键重复问题。</p>

	

	

</article>




	<article>
	
		<h1><a href="/2017/10/12/MySQL/3. 源码编译安装MySQL5.6报错及解决方法/">源码编译安装MySQL5.6报错及解决方法</a></h1>
	
	<div class="article__infos">
		<span class="article__date">2017-10-12</span><br />
		
		
			<span class="article__tags">
			  	<a class="article__tag-link" href="/tags/MySQL/">MySQL</a>
			</span>
		
	</div>

	

	
		<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;以前都是mysql5.1做的LAMP搭建，比较顺利。试了一下安装mysql5.6.</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;由于系统没有cmake命令，于是 <code>yum install -y cmake</code> 进行安装</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在cmake这里</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">cmake -DCMAKE_INSTALL_PREFIX=/usr/<span class="built_in">local</span>/mysql -DMYSQL_DATADIR=/data/mysql_data -DMYSQL_UNIX_ADDR=/usr/<span class="built_in">local</span>/mysql/mysql.sock -DSYSCONFDIR=/etc -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DMYSQL_TCP_PORT=3306 -DENABLED_LOCAL_INFILE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;报错如下：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div></pre></td><td class="code"><pre><div class="line">-- Could NOT find Curses (missing: CURSES_LIBRARY CURSES_INCLUDE_PATH)</div><div class="line">CMake Error at cmake/readline.cmake:82 (MESSAGE):</div><div class="line"> Curses library not found. Please install appropriate package,</div><div class="line">    remove CMakeCache.txt and rerun cmake.On Debian/Ubuntu, package name is libncurses5-dev, on Redhat and derivates it is ncurses-devel.</div><div class="line">Call Stack (most recent call first):</div><div class="line"> cmake/readline.cmake:126 (FIND_CURSES)</div><div class="line"> cmake/readline.cmake:216 (MYSQL_USE_BUNDLED_LIBEDIT)</div><div class="line"> CMakeLists.txt:250 (MYSQL_CHECK_READLINE)</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;解决办法：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">[root@localhost mysql-5.6.1]<span class="comment"># rm CMakeCache.txt</span></div><div class="line">[root@localhost mysql-5.6.1]<span class="comment"># yum install -y ncurses-devel</span></div><div class="line">[root@localhost mysql-5.5.11]<span class="comment"># yum install -y bison</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;接着继续执行编译</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">cmake......</div><div class="line">make &amp;&amp; make install</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;编译完成，安装好之后。新建系统用户mysql，并禁止登录</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">useradd -s /sbin/nologin mysql</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;建立数据库存放目录</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">mkdir -p /data/mysql_data</div><div class="line">chown -R mysql:mysql /data/mysql_data</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;进行数据库初始化</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line"><span class="built_in">cd</span> /usr/<span class="built_in">local</span>/mysql</div><div class="line">./scripts/mysql_install_db --user=mysql --datadir=/data/mysql_data</div><div class="line">cp support-files/my-large.cnf /etc/my.cnf </div><div class="line">cp support-files/mysql.server /etc/init.d/mysqld</div><div class="line">chmod 755 /etc/init.d/mysqld </div><div class="line">vim /etc/init.d/mysqld //添加datadir=路径</div><div class="line">service mysqld start -------------------------------服务启动报错</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;报错信息如下</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">[root@centos6 mysql_data]<span class="comment"># service mysqld start</span></div><div class="line">Starting MySQL...The server quit without updating PID file [失败]/mysql_data/centos6.6.pid).</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;解决方法：根据报错信息，怀疑可能文件丢失造成的原因，对比另一个测试机已经搭建好的环境，检查同样的数据库目录下，发现本机少了一个centos6.6.pid文件</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div></pre></td><td class="code"><pre><div class="line"><span class="built_in">cd</span> /data/mysql_data //数据库文件存放目录</div><div class="line">vi centos6.6.pid //编辑此文件，添加一个pid号码</div><div class="line">1583 </div><div class="line">保存退出。 //1583是我在系统中随意添加的号，只要保证系统进程里没有的ID都可以</div><div class="line">service mysqld start 正常启动 ----------------------------成功解决</div></pre></td></tr></table></figure>
	

	

</article>




	<article>
	
		<h1><a href="/2017/10/12/MySQL/28. cobar实现mysql分库分表/">cobar实现mysql分库分表</a></h1>
	
	<div class="article__infos">
		<span class="article__date">2017-10-12</span><br />
		
		
			<span class="article__tags">
			  	<a class="article__tag-link" href="/tags/MySQL/">MySQL</a>
			</span>
		
	</div>

	

	
		<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;cobar 编译安装配置笔记:<a href="https://github.com/alibaba/cobar" target="_blank" rel="external">https://github.com/alibaba/cobar</a></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;windows下使用eclipse导入cobar项目,eclipse File -&gt; Import -&gt; Git :<a href="https://github.com/alibaba/cobar" target="_blank" rel="external">https://github.com/alibaba/cobar</a></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;linux下：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">wget https://codeload.github.com/alibaba/cobar/zip/master</div></pre></td></tr></table></figure>
<p>F:\mycat&gt;mvn compile<br>F:\mycat&gt;mvn package</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;生成压缩包cobar-server-1.2.7.tar.gz，放到linux环境中解压出来，没有logs目录，新建并运行查看目录结构如下：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div></pre></td><td class="code"><pre><div class="line">[root@XAYQ-Test3 ~]<span class="comment"># tree /opt/cobar-server  </span></div><div class="line">/opt/software/cobar-server  </div><div class="line">├── bin  </div><div class="line">│   ├── restart.sh  </div><div class="line">│   ├── shutdown.sh  </div><div class="line">│   ├── startup.bat  </div><div class="line">│   └── startup.sh  </div><div class="line">├── conf  </div><div class="line">│   ├── log4j.xml                       <span class="comment">#日志配置文件，不需要修改  </span></div><div class="line">│   ├── rule.xml                        <span class="comment">#mysql路由规则  </span></div><div class="line">│   ├── schema.xml                      <span class="comment">#  </span></div><div class="line">│   └── server.xml                      <span class="comment">#  </span></div><div class="line">├── lib  </div><div class="line">│   ├── cobar-server-1.2.7.jar  </div><div class="line">│   └── log4j-1.2.17.jar  </div><div class="line">└── logs  </div><div class="line">    ├── alarm.log  </div><div class="line">    ├── console.log  </div><div class="line">    ├── heartbeat.log  </div><div class="line">    ├── stdout.log  </div><div class="line">    └── stdout.log.2014-07-10</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;1.主要修改以下几个文件：rule.xml </p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;<!-- 路由规则定义，定义什么表，什么字段，采用什么路由算法 -->  </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div><div class="line">24</div><div class="line">25</div><div class="line">26</div><div class="line">27</div><div class="line">28</div><div class="line">29</div><div class="line">30</div><div class="line">31</div><div class="line">32</div><div class="line">33</div><div class="line">34</div><div class="line">35</div><div class="line">36</div><div class="line">37</div><div class="line">38</div><div class="line">39</div><div class="line">40</div><div class="line">41</div><div class="line">42</div><div class="line">43</div><div class="line">44</div><div class="line">45</div><div class="line">46</div><div class="line">47</div><div class="line">48</div><div class="line">49</div><div class="line">50</div><div class="line">51</div><div class="line">52</div><div class="line">53</div><div class="line">54</div></pre></td><td class="code"><pre><div class="line">&lt;tableRule name=<span class="string">"rule1"</span>&gt;  </div><div class="line">  &lt;rule&gt;  </div><div class="line">    &lt;columns&gt;id&lt;/columns&gt;  </div><div class="line">    &lt;algorithm&gt;&lt;![CDATA[ func1(<span class="variable">$&#123;id&#125;</span>) ]]&gt;&lt;/algorithm&gt;  </div><div class="line">  &lt;/rule&gt;  </div><div class="line">&lt;/tableRule&gt;  </div><div class="line">  </div><div class="line">&lt;!-- 路由函数定义 --&gt;  </div><div class="line">&lt;<span class="keyword">function</span> name=<span class="string">"func1"</span> class=<span class="string">"com.alibaba.cobar.route.function.PartitionByLong"</span>&gt;  </div><div class="line">  &lt;property name=<span class="string">"partitionCount"</span>&gt;2&lt;/property&gt;  </div><div class="line">  &lt;property name=<span class="string">"partitionLength"</span>&gt;512&lt;/property&gt;  </div><div class="line">&lt;/<span class="keyword">function</span>&gt;  </div><div class="line">```  </div><div class="line"></div><div class="line">&amp;<span class="comment">#160;&amp;#160;&amp;#160;&amp;#160;&amp;#160;&amp;#160;&amp;#160;&amp;#160;2.schema.xml ，定义数据节点</span></div><div class="line"></div><div class="line">```bash</div><div class="line">&lt;cobar:schema xmlns:cobar=<span class="string">"http://cobar.alibaba.com/"</span>&gt;  </div><div class="line">  </div><div class="line">  &lt;!-- schema定义 --&gt;  </div><div class="line">  &lt;schema name=<span class="string">"cppdb"</span> dataNode=<span class="string">"cppDb1"</span>&gt;  </div><div class="line">    &lt;table name=<span class="string">"tb2"</span> dataNode=<span class="string">"cppDb2,cppDb3"</span> rule=<span class="string">"rule1"</span> /&gt;  </div><div class="line">  &lt;/schema&gt;  </div><div class="line">  </div><div class="line">  &lt;!-- 数据节点定义，数据节点由数据源和其他一些参数组织而成。--&gt;  </div><div class="line">  &lt;dataNode name=<span class="string">"cppDb1"</span>&gt;  </div><div class="line">    &lt;property name=<span class="string">"dataSource"</span>&gt;  </div><div class="line">      &lt;dataSourceRef&gt;cppDataSource[0]&lt;/dataSourceRef&gt;  </div><div class="line">    &lt;/property&gt;  </div><div class="line">  &lt;/dataNode&gt;  </div><div class="line">  &lt;dataNode name=<span class="string">"cppDb2"</span>&gt;  </div><div class="line">    &lt;property name=<span class="string">"dataSource"</span>&gt;  </div><div class="line">      &lt;dataSourceRef&gt;cppDataSource[1]&lt;/dataSourceRef&gt;  </div><div class="line">    &lt;/property&gt;  </div><div class="line">  &lt;/dataNode&gt;  </div><div class="line">  &lt;dataNode name=<span class="string">"cppDb3"</span>&gt;  </div><div class="line">    &lt;property name=<span class="string">"dataSource"</span>&gt;  </div><div class="line">      &lt;dataSourceRef&gt;cppDataSource[2]&lt;/dataSourceRef&gt;  </div><div class="line">    &lt;/property&gt;  </div><div class="line">  &lt;/dataNode&gt;  </div><div class="line">  </div><div class="line">  &lt;!-- 数据源定义，数据源是一个具体的后端数据连接的表示。--&gt;  </div><div class="line">  &lt;dataSource name=<span class="string">"cppDataSource"</span> <span class="built_in">type</span>=<span class="string">"mysql"</span>&gt;  </div><div class="line">    &lt;property name=<span class="string">"location"</span>&gt;  </div><div class="line">      &lt;location&gt;172.22.14.7:3306/cpp1&lt;/location&gt;  </div><div class="line">      &lt;location&gt;172.22.14.7:3306/cpp2&lt;/location&gt;  </div><div class="line">      &lt;location&gt;172.22.14.7:3306/cpp3&lt;/location&gt;  </div><div class="line">    &lt;/property&gt;  </div><div class="line">    &lt;property name=<span class="string">"user"</span>&gt;root&lt;/property&gt;  </div><div class="line">    &lt;property name=<span class="string">"password"</span>&gt;root&lt;/property&gt;  </div><div class="line">    &lt;property name=<span class="string">"sqlMode"</span>&gt;STRICT_TRANS_TABLES&lt;/property&gt;  </div><div class="line">  &lt;/dataSource&gt;  </div><div class="line">  </div><div class="line">&lt;/cobar:schema&gt;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;3.server.xml，定义cobar对外统一的数据接口。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line">  &lt;!-- 用户访问定义，用户名、密码、schema等信息。 --&gt;</div><div class="line"></div><div class="line"></div><div class="line">&lt;user name=<span class="string">"root"</span>&gt;  </div><div class="line">&lt;property name=<span class="string">"password"</span>&gt;root&lt;/property&gt;  </div><div class="line">&lt;property name=<span class="string">"schemas"</span>&gt;cppdb&lt;/property&gt;  </div><div class="line">t;/user&gt;</div></pre></td></tr></table></figure>

	

	

</article>




	<article>
	
		<h1><a href="/2017/10/12/MySQL/29. mysql AB 常见错误/">mysql AB 常见错误</a></h1>
	
	<div class="article__infos">
		<span class="article__date">2017-10-12</span><br />
		
		
			<span class="article__tags">
			  	<a class="article__tag-link" href="/tags/MySQL/">MySQL</a>
			</span>
		
	</div>

	

	
		<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;这篇文章旨在记录MySQL Replication的常见错误，包括自己工作中遇到的与网友在工作中遇到的，方面自己及别人以后进行查找。每个案例都是通过Last_IO_Errno/Last_IO_Error或者Last_SQL_Errno/Last_SQL_Error给出错误关键信息，所以以后查找时只需直接ctrl+F查找关键字就行。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">Last_SQL_Errno: 1677</div><div class="line">Last_SQL_Error: Column 1 of table <span class="string">'test.t'</span> cannot be converted from <span class="built_in">type</span> <span class="string">'int'</span> to <span class="built_in">type</span> <span class="string">'bigint(20)'</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;解决方法：这个案例是从网上找到的，自己动手实验了一把。从错误信息来看表面上是由于在slave上无法执行一条转换字段类型的SQL语句。实际上并不是有这种语句直接引起的，而是间接引起的（之前某些操作导致主从表字段类型不一致，接下来对这个表进行DML时就会报错）。它的意思是在对这个表t进行DML操作时，发现主从上表结果不一致，比如这里是说在主上t的字段1是int类型，但是从上t的字段1是bigint类型，所以报错。那么为什么要报错呢？这是从安全角度考虑，因为如果字段类型不一致可能会导致数据截断之类的问题。那么解决方法呢？通过参数slave_type_conversions进行控制，它有三种取值：</p>
<ul>
<li>ALL_LOSSY：仅支持有损转换，什么叫有损？比如一个值本来是bigint存储为9999999999999，现在转换为int类型势必会要截断从而导致数据不一致。</li>
<li>ALL_NON_LOSSY：仅支持无损转换，只能在无损的情况下才能进行转换</li>
<li>ALL_LOSSY,ALL_NON_LOSSY：有损/无算转换都支持</li>
</ul>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;空，即不设置这个参数：必须主从的字段类型一模一样。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;注意： 前面说的这几中情况都只在binlog_format=ROW的情况下才有效。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">Last_SQL_Errno: 1194</div><div class="line">Last_SQL_Error: Error <span class="string">'Table '</span>traincenter<span class="string">' is marked as crashed and should be repaired'</span> on query. Default database: <span class="string">'basketballman'</span>. Query: <span class="string">'update traincenter set points='</span>4<span class="string">',pointstime='</span>1361912066<span class="string">'  where uid = '</span>1847482697<span class="string">' limit 1'</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;解决方法：myisam表traincenter损坏，直接repair table即可。至于为什么myisam类型表比innodb更容易损坏，我觉得有两个原因：1，innodb有double write机制，损坏或者half write的页可以用它恢复，第二innodb是事务引擎，都有操作都是事务的，而myisam是非事务的，存在写一半但是操作终止情况。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">Last_IO_Errno: 1236</div><div class="line">Last_IO_Error: Got fatal error 1236 from master when reading data from binary <span class="built_in">log</span>: <span class="string">'Could not find first log file name in binary log index file'</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;解决方法：主库上的binlog文件已经不存在但是在index file中确有相应记录存在。我这里发生这个错误的原因在于由于复制中断时间很长，报警出来一直没人处理，这个中断时间超过master上binlog超期时间，等恢复复制时需要的binlog已经由于其超期而被删掉，没办法只好重建这个实例了。以大家都要引以为戒。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">Last_IO_Errno: 1593</div><div class="line">Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different <span class="keyword">for</span> replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;解决方法：主从配置的server-id一样，而在主从复制环境中server-id一样的binlog events都会被过滤掉。具体server-id的含义可以了解一下复制原理。这个一般是因为拷贝配置文件时忘记修改server-id导致，遇到这类问题也比较容易，平时操作谨慎一点即可。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">Last_Errno: 1053</div><div class="line">Last_Error: Query partially completed on the master (error on master: 1053) and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and <span class="keyword">then</span> restart the slave with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; . Query: <span class="string">'insert into ...</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;解决方法：查询在master上部分完成，然后终止了。这马上又能想到是myisam表，结果也正是这样。由于myisam不支持事务所以可能存在一个查询完成一部分然后失败的情况。解决方法一般也就是提示信息给出的跳过一个binlog event。不过确认跳过之前最好还是查询一下master上是否真的存在相应的记录，因为错误信息同时还会给出它认为在master上执行一部分然后终止的查询语句。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">Last_SQL_Errno: 1666</div><div class="line">Last_SQL_Error: Error executing row event: <span class="string">'Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.'</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;解决方法：这个案例的背景是做一个ABC结构的复制，B、C中设定的binlog_format=statement，A中的是MIXED，所以当B尝试重做A过来的relay log，然后记录binlog（传给C）时发现relay log的binlog_format与自己设定的binlog_format不一致。我当时就是直接先更改BC的binlog_format=MIXED解决。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">Last_Errno: 1032</div><div class="line">Last_Error: Could not execute Update_rows event on table db.table; Can<span class="string">'t find record in '</span>table<span class="string">', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event'</span>s master <span class="built_in">log</span> mysql-bin.000064, end_log_pos 158847</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;解决方法：这个是在binlog_format=row复制下发生的。原因是因为row格式复制是最严格的，所以在mysql看来如果在从库上找不到要更新的这条记录，那么就代表主从数据不一致，因此报错。另外顺便说一句，对于row格式binlog，如果某个更新操作实际上并没有更新行，这个操作是不会记binlog的，因为row格式的binlog宗旨就是只记录发生了改变的行。所以这个解决办法根据你自己实际应用来定，最好的方法还是重做slave吧，这样更放心。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">Last_Errno: 28</div><div class="line">Last_Error: Error <span class="keyword">in</span> Append_block event: write to <span class="string">'/tmp/SQL_LOAD-32343798-72213798-1.data'</span> failed</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;解决方法： 首先说错误原因：主库执行load data infile，同步到从库后load data infile存放的文件默认是放在/tmp(由参数slave_load_tmpdir控制)，而/tmp空间不够因此报错。因此只要将从库上slave_load_tmpdir设置到一个磁盘空间足够大的分区就行。</p>

	

	

</article>




	<article>
	
		<h1><a href="/2017/10/12/MySQL/27. Mysql主主复制架构配置/">Mysql主主复制架构配置</a></h1>
	
	<div class="article__infos">
		<span class="article__date">2017-10-12</span><br />
		
		
			<span class="article__tags">
			  	<a class="article__tag-link" href="/tags/MySQL/">MySQL</a>
			</span>
		
	</div>

	

	
		<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;MySQL主主复制结构区别于主从复制结构。在主主复制结构中，两台服务器的任</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;何一台上面的数据库存发生了改变都会同步到另一台服务器上，这样两台服务器</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;互为主从，并且都能向外提供服务。 这就比使用主从复制具有更好的性能。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;接下来我将使用两个同样的服务器来实现这个效果：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">server1_mysql：192.168.1.108</div><div class="line">server2_mysql: 192.168.1.110</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;拓扑结构：<br>server1_mysql——-server2_mysql</p>
<h2 id="1-创建用户并授权"><a href="#1-创建用户并授权" class="headerlink" title="1.创建用户并授权"></a>1.创建用户并授权</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;server1:</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">mysql&gt; GRANT REPLICATION SLAVE ON *.* TO <span class="string">'server2'</span>@<span class="string">'192.168.1.110'</span> </div><div class="line">IDENTIFIED BY <span class="string">'server2'</span>;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;server2:</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">mysql&gt; GRANT REPLICATION SLAVE ON *.* TO <span class="string">'server1'</span>@<span class="string">'192.168.1.108'</span> </div><div class="line">IDENTIFIED BY <span class="string">'server1'</span>;</div></pre></td></tr></table></figure>
<h2 id="2-修改Mysql的主配置文件"><a href="#2-修改Mysql的主配置文件" class="headerlink" title="2.修改Mysql的主配置文件"></a>2.修改Mysql的主配置文件</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;server1:</p>
 <figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div></pre></td><td class="code"><pre><div class="line">[mysqld]</div><div class="line">server-id = 10</div><div class="line"><span class="built_in">log</span>-bin = mysql-bin</div><div class="line">replicate-do-db = mydb</div><div class="line">auto-increment-increment = 2   //每次增长2</div><div class="line">auto-increment-offset = 1  //设置自动增长的字段的偏移量，即初始值为2</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;启动Mysql服务：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line"><span class="comment"># service mysqld restart</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;server2:</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div></pre></td><td class="code"><pre><div class="line">[mysqld]</div><div class="line"> server-id = 20</div><div class="line"> <span class="built_in">log</span>-bin = mysql-bin</div><div class="line"> replicate-do-db = mydb</div><div class="line"> auto-increment-increment = 2  //每次增长2</div><div class="line"> auto-increment-offset = 2 //设置自动增长的字段的偏移量，即初始值为2</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;启动Mysql服务：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line"><span class="comment"># service mysqld restart</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;注：二都只有server-id不同和 auto-increment- offset不同.auto-increment-increment的值应设为整个结构中服务器的总数，本案例用到两台服务器，所以值设为2。</p>
<h2 id="3-重新启动两个服务器"><a href="#3-重新启动两个服务器" class="headerlink" title="3.重新启动两个服务器"></a>3.重新启动两个服务器</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line"><span class="comment"># service mysqld restart</span></div></pre></td></tr></table></figure>
<h2 id="4-为了让两个数据库一样，我们备份其中一个数据库，然后在另一个数据库上恢复，这样是两个数据库一开始都是一样的。"><a href="#4-为了让两个数据库一样，我们备份其中一个数据库，然后在另一个数据库上恢复，这样是两个数据库一开始都是一样的。" class="headerlink" title="4.为了让两个数据库一样，我们备份其中一个数据库，然后在另一个数据库上恢复，这样是两个数据库一开始都是一样的。"></a>4.为了让两个数据库一样，我们备份其中一个数据库，然后在另一个数据库上恢复，这样是两个数据库一开始都是一样的。</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在server1上操作:</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line"><span class="comment"># mysqldump --databases luowei &gt; /tmp/luowei.sql</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在server2上操作：创建一个与mydb同名的空数据库</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div></pre></td><td class="code"><pre><div class="line"><span class="comment"># mysql</span></div><div class="line">    &gt; CREATE DATABASE mydb;</div><div class="line">    &gt;\q</div><div class="line"><span class="comment"># scp 192.168.1.108:/tmp/mydb.sql  ./</span></div><div class="line"><span class="comment"># mysql -uroot -p mydb &lt; /tmp/luowei.sql</span></div></pre></td></tr></table></figure>
<h2 id="5-然后两个服务器相互通告二进制日志的位置并启动复制功能："><a href="#5-然后两个服务器相互通告二进制日志的位置并启动复制功能：" class="headerlink" title="5.然后两个服务器相互通告二进制日志的位置并启动复制功能："></a>5.然后两个服务器相互通告二进制日志的位置并启动复制功能：</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在server1上：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div></pre></td><td class="code"><pre><div class="line"><span class="comment"># mysql</span></div><div class="line">   &gt; CHANGE MASTER TO</div><div class="line">   &gt; MASTER_HOST=<span class="string">'192.168.1.110'</span>,</div><div class="line">   &gt; MASTER_USER=<span class="string">'server2'</span>,</div><div class="line">   &gt; MASTER_PASSWORD=<span class="string">'server2'</span>;</div><div class="line">mysql &gt; START SLAVE;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在server2上：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div></pre></td><td class="code"><pre><div class="line"><span class="comment"># mysql</span></div><div class="line">   &gt; CHANGE MASTER TO</div><div class="line">   &gt; MASTER_HOST=<span class="string">'192.168.1.108'</span>,</div><div class="line">   &gt; MASTER_USER=<span class="string">'server1'</span>,</div><div class="line">   &gt; MASTER_PASSWORD=<span class="string">'server1'</span>;</div><div class="line">mysql &gt; START SLAVE;</div></pre></td></tr></table></figure>
<h2 id="6-查看，并验证"><a href="#6-查看，并验证" class="headerlink" title="6.查看，并验证"></a>6.查看，并验证</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;分别在两个数据库服务器上查看</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql &gt; SHOW SLAVE STATUS\G;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;然后查看数据库和表，你会发现内容是一样的，这样就是整个主主Mysql的架构的配置过程。</p>

	

	

</article>




	<article>
	
		<h1><a href="/2017/10/12/MySQL/25. Mysql分库分表方案/">Mysql分库分表方案</a></h1>
	
	<div class="article__infos">
		<span class="article__date">2017-10-12</span><br />
		
		
			<span class="article__tags">
			  	<a class="article__tag-link" href="/tags/MySQL/">MySQL</a>
			</span>
		
	</div>

	

	
		<h2 id="为什么要分表："><a href="#为什么要分表：" class="headerlink" title="为什么要分表："></a>为什么要分表：</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;当一张表的数据达到几千万时，你查询一次所花的时间会变多，如果有联合查询的话，我想有可能会死在那儿了。分表的目的就在于此，减小数据库的负担，缩短查询时间。        </p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;mysql中有一种机制是表锁定和行锁定，是为了保证数据的完整性。表锁定表示你们都不能对这张表进行操作，必须等我对表操作完才行。行锁定也一样，别的sql必须等我对这条数据操作完了，才能对这条数据进行操作。</p>
<h2 id="mysql-proxy：amoeba"><a href="#mysql-proxy：amoeba" class="headerlink" title="mysql proxy：amoeba"></a>mysql proxy：amoeba</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;做mysql集群,利用amoeba。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;从上层的java程序来讲，不需要知道主服务器和从服务器的来源，即主从数据库服务器对于上层来讲是透明的。可以通过amoeba来配置。</p>
<h2 id="大数据量并且访问频繁的表，将其分为若干个表"><a href="#大数据量并且访问频繁的表，将其分为若干个表" class="headerlink" title="大数据量并且访问频繁的表，将其分为若干个表"></a>大数据量并且访问频繁的表，将其分为若干个表</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;比如对于某网站平台的数据库表-公司表，数据量很大，这种能预估出来的大数据量表，我们就事先分出个N个表，这个N是多少，根据实际情况而定。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;某网站现在的数据量至多是5000万条，可以设计每张表容纳的数据量是500万条，也就是拆分成10张表，</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;那么如何判断某张表的数据是否容量已满呢？可以在程序段对于要新增数据的表，在插入前先做统计表记录数量的操作，当&lt;500万条数据，就直接插入，当已经到达阀值，可以在程序段新创建数据库表（或者已经事先创建好），再执行插入操作。</p>
<h2 id="利用merge存储引擎来实现分表"><a href="#利用merge存储引擎来实现分表" class="headerlink" title="利用merge存储引擎来实现分表"></a>利用merge存储引擎来实现分表</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;如果要把已有的大数据量表分开比较痛苦，最痛苦的事就是改代码，因为程序里面的sql语句已经写好了。用merge存储引擎来实现分表, 这种方法比较适合.<br>举例子：</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/Mysql%E5%88%86%E5%BA%93%E5%88%86%E8%A1%A8%E6%96%B9%E6%A1%88/01.jpeg?raw=true" alt=""></figure></p>
<h2 id="数据库架构"><a href="#数据库架构" class="headerlink" title="数据库架构"></a>数据库架构</h2><h3 id="1、简单的MySQL主从复制"><a href="#1、简单的MySQL主从复制" class="headerlink" title="1、简单的MySQL主从复制:"></a>1、简单的MySQL主从复制:</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;MySQL的主从复制解决了数据库的读写分离，并很好的提升了读的性能，其图如下：</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/Mysql%E5%88%86%E5%BA%93%E5%88%86%E8%A1%A8%E6%96%B9%E6%A1%88/02.jpeg?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;其主从复制的过程如下图所示：</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/Mysql%E5%88%86%E5%BA%93%E5%88%86%E8%A1%A8%E6%96%B9%E6%A1%88/03.jpeg?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;但是，主从复制也带来其他一系列性能瓶颈问题：</p>
<ol>
<li>写入无法扩展</li>
<li>写入无法缓存</li>
<li>复制延时</li>
<li>锁表率上升</li>
<li>表变大，缓存率下降</li>
</ol>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;那问题产生总得解决的，这就产生下面的优化方案，一起来看看。</p>
<h3 id="2、MySQL垂直分区"><a href="#2、MySQL垂直分区" class="headerlink" title="2、MySQL垂直分区"></a>2、MySQL垂直分区</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;如果把业务切割得足够独立，那把不同业务的数据放到不同的数据库服务器将是一个不错的方案，而且万一其中一个业务崩溃了也不会影响其他业务的正常进行，并且也起到了负载分流的作用，大大提升了数据库的吞吐能力。经过垂直分区后的数据库架构图如下：</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/Mysql%E5%88%86%E5%BA%93%E5%88%86%E8%A1%A8%E6%96%B9%E6%A1%88/04.jpeg?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;然而，尽管业务之间已经足够独立了，但是有些业务之间或多或少总会有点联系，如用户，基本上都会和每个业务相关联，况且这种分区方式，也不能解决单张表数据量暴涨的问题，因此为何不试试水平分割呢？</p>
<h3 id="3、MySQL水平分片（Sharding）"><a href="#3、MySQL水平分片（Sharding）" class="headerlink" title="3、MySQL水平分片（Sharding）"></a>3、MySQL水平分片（Sharding）</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;这是一个非常好的思路，将用户按一定规则（按id哈希）分组，并把该组用户的数据存储到一个数据库分片中，即一个sharding，这样随着用户数量的增加，只要简单地配置一台服务器即可，原理图如下：</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/Mysql%E5%88%86%E5%BA%93%E5%88%86%E8%A1%A8%E6%96%B9%E6%A1%88/05.jpeg?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;如何来确定某个用户所在的shard呢，可以建一张用户和shard对应的数据表，每次请求先从这张表找用户的shard id，再从对应shard中查询相关数据，如下图所示：</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/Mysql%E5%88%86%E5%BA%93%E5%88%86%E8%A1%A8%E6%96%B9%E6%A1%88/06.jpeg?raw=true" alt=""></figure></p>

	

	

</article>





	<span class="different-posts">📖 <a href="/page/29">more posts</a> 📖</span>



	</main>

	<footer class="footer">
	<div class="footer-content">
		
	      <div class="footer__element">
	<p>Hi there, <br />welcome to my Blog glad you found it. Have a look around, will you?</p>
</div>

	    
	      <div class="footer__element">
	<h5>Check out</h5>
	<ul class="footer-links">
		<li class="footer-links__link"><a href="/archives">Archive</a></li>
		
		  <li class="footer-links__link"><a href="/atom.xml">RSS</a></li>
	    
		<li class="footer-links__link"><a href="/about">about page</a></li>
		<li class="footer-links__link"><a href="/tags">Tags</a></li>
		<li class="footer-links__link"><a href="/categories">Categories</a></li>
	</ul>
</div>

	    

		<div class="footer-credit">
			<span>© 2017 失落的乐章 | Powered by <a href="https://hexo.io/">Hexo</a> | Theme <a href="https://github.com/HoverBaum/meilidu-hexo">MeiliDu</a></span>
		</div>

	</div>


</footer>



</body>

</html>
